package com.ruoyi.cases.utils.excel;

import java.nio.charset.StandardCharsets;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractXlsxView;

/**
 * 生成excel视图，可用excel工具打开或者保存 由ViewController的return new ModelAndView(viewExcel, model)生成
 */
public class ExcelExport extends AbstractXlsxView {

    /**
     * 创建Excel
     *
     * @param model    模块
     * @param workbook Excel属性
     * @param request  请求
     * @param response 请求
     * @throws Exception 异常
     */
    private void gotoCreateExcel(Map model, Workbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
        String excelName =
            model.get("fileName") + "_" + DateUtils.dateTimeNow("yyyyMMddHHmmss") + ".xlsx";
        // 设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开
        response.setContentType("APPLICATION/OCTET-STREAM;charset=UTF-8");
        String agent = request.getHeader("USER-AGENT");
        if (null != agent && -1 != agent.indexOf("MSIE") || null != agent && -1 != agent
            .indexOf("Trident")) {
            excelName = java.net.URLEncoder.encode(excelName, "UTF8");

        } else if (null != agent && -1 != agent.indexOf("Mozilla")) {
            excelName = new String(excelName.getBytes(StandardCharsets.UTF_8),
                StandardCharsets.ISO_8859_1);
        }

        response.setHeader("Content-Disposition", "attachment; filename=" + excelName);

        // 产生Excel表头
        XSSFSheet sheet = (XSSFSheet) workbook.createSheet();
        XSSFRow header = sheet.createRow(0); // 第0行
        // 产生标题列
        String[] titles = (String[]) model.get("titles");//名称
        String[] names = (String[]) model.get("names");//字段

        for (int n = 0; n < titles.length; n++) {
            header.createCell(n).setCellValue(titles[n]);
        }
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("TEXT"));//字符串
        int rowNum = 1;
        List<Map<String, String>> stuList = (List<Map<String, String>>) model.get("list");
        addCell(sheet, names, cellStyle, rowNum, stuList);

    }

    /**
     * 添加单元格
     *
     * @param sheet     单元格
     * @param names     名字
     * @param cellStyle 样式
     * @param rowNum    行号
     * @param stuList   数据
     */
    static void addCell(XSSFSheet sheet, String[] names, CellStyle cellStyle, int rowNum,
        List<Map<String, String>> stuList) {
        for (int i = 0; i < stuList.size(); i++) {
            Map<String, String> map = stuList.get(i);
            Row row = sheet.createRow(rowNum++);
            //循环每个单元格填充
            for (int n = 0; n < names.length; n++) {
                String name = names[n];
                Cell cell = row.createCell(n);
                cell.setCellStyle(cellStyle);
                String value = String.valueOf(map.get(name));
                if (StringUtils.isNotBlank(value) && !"null".equalsIgnoreCase(value)) {
                    cell.setCellValue(value);
                }
            }
        }
    }

    /**
     * 添加单元格
     *
     * @param sheet     单元格
     * @param names     名字
     * @param cellStyle 样式
     * @param rowNum    行号
     * @param stuList   数据
     */
    static void addCell(HSSFSheet sheet, String[] names, CellStyle cellStyle, int rowNum,
        List<Map<String, String>> stuList) {
        /*工区统计 大于18.5 考核结果为合格*/
        Double total = 0.0;
        for (int i = 0; i < stuList.size(); i++) {
            Map<String, String> map = stuList.get(i);
            Row row = sheet.createRow(rowNum++);
            //循环每个单元格填充
            for (int n = 0; n < names.length; n++) {
                String name = names[n];
                Cell cell = row.createCell(n);
                cell.setCellStyle(cellStyle);
                if(i<stuList.size()-2){
                    String value = String.valueOf(map.get(name));
                    /*计算完成系数总和*/
                    if(n == 17)
                    total = total + Double.parseDouble(value.equals("null")?"0":value);
                    if (StringUtils.isNotBlank(value) && !"null".equalsIgnoreCase(value)) {
                        if (value.startsWith("cellFormula=")) {
                            if (value.split("=").length > 1) {
                                cell.setCellFormula(value.split("=")[1]);
                            } else {
                                cell.setCellValue("");
                            }
                        } else {
                            cell.setCellValue(value);
                        }
                    }
                    /*特殊处理对号问题*/
                    if(n == 3 && "type".equals(name)){
                        if("3".equals(String.valueOf(map.get(name)))){
                            cell.setCellValue("\u221A");
                        }else{
                            cell.setCellValue("");
                        }
                    }
                    if(n == 4 && "type".equals(name)){
                        if("2".equals(String.valueOf(map.get(name)))){
                            cell.setCellValue("\u221A");
                        }else{
                            cell.setCellValue("");
                        }
                    }
                    if(n == 5 && "type".equals(name)){
                        if("1".equals(String.valueOf(map.get(name)))){
                            cell.setCellValue("\u221A");
                        }else{
                            cell.setCellValue("");
                        }
                    }
                    if(n == 18 && "auditResult".equals(name)){
                        if("达标".equals(String.valueOf(map.get(name)))){
                            cell.setCellValue("\u221A");
                        }else{
                            cell.setCellValue("");
                        }
                    }
                    if(n == 19 && "auditResult".equals(name)){
                        if("未达标".equals(String.valueOf(map.get(name)))){
                            cell.setCellValue("\u221A");
                        }else{
                            cell.setCellValue("");
                        }
                    }
                }else{
                    if(n==14){
                        cell.setCellValue("日均系数统计");
                    }else if(n==17){

                            cell.setCellValue(String.format("%.2f",total));

                    }
                }

            }
        }
    }

    @Override
    protected void buildExcelDocument(Map<String, Object> map, Workbook workbook,
        HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse)
        throws Exception {
        gotoCreateExcel(map, workbook, httpServletRequest, httpServletResponse);

    }
}
